package jm.nj.demo23main.c_dbUtils;

import jm.nj.demo23main.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.*;
import org.junit.Test;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import java.util.Set;

public class DbUiltsCurdDemo {

    @Test
    public void insert() throws SQLException {
        //创建QueryRunner
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());

        int update = runner.update("insert into user (username , password)value(?,?)","jack","15498");
        System.out.println(update);


    }
    @Test
    public void insert01() throws SQLException {
        // 创建QueryRunner
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        // 执行sql语句
        int update = runner.update("insert into user (username,password) value (?,?)", "jack", "123");
        System.out.println(update);
    }

    @Test
    public void insert02() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());

        Object[] params = {"yuy","6"};
        int update = runner.update("insert into user (username, password) values (?, ?)", params);
        System.out.println(update);
    }

    @Test
    public void insert03() throws SQLException{
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());

        Object[] params = {"yuy","6"};
        int update = runner.update("update user set password = ? where username = ? ","" +
                "99999","yuy");
        System.out.println(update);
    }
    /**
     * BeanHandler:把ResultSet里的第一行数据转换成javaBean
     *
     * @throws SQLException
     */
    @Test
    public void query01() throws SQLException {
        //创建Queryrunner
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        User user = runner.query("select * from user",new BeanHandler<User>(User.class));
        System.out.println(user);

    }

    /*
    * BeanListHandler:把ResultSet的所有的数据转换为List,集合里 的元素是javaBean
    *
    * @throws SQLExcetion
    * */
    @Test
    public void query2() throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        List<User> list = runner.query("select * from user",new BeanListHandler<User>(User.class));
        System.out.println(list);
    }

    /*
    * MapHandler : 把ResultSet的第一行数据,装换成Map<columnName,columnValue>

    * @throws SQLException
    * */
    @Test
    public void query3() throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        Map<String, Object> map = runner.query("select username,password from user ", new MapHandler());
        System.out.println(map);
        System.out.println(map.keySet());

    }
/**
 * MapListHandler:把ResultSet的全部数据转换成List，List里的元素是Map<columnname,
 * columnValue>，代表每一行的数据
 *MapListHandler :把ResultSet的全部数据转换成List,List 里的元素Map<columnname><columnValue>
 *     代表每一行数据
 * @throws SQLException
 */
    @Test
    public  void query04() throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());

        List<Map<String,Object>>list= runner.query("select * from user ",new MapListHandler());
        System.out.println(list);
        System.out.println(list.get(0).keySet());
    }
/**
 * ScalarHandler: 把ResultSet的第一行第一列数据取出来
 *ScalarHandler
 * @throws SQLException
 */
    @Test
    public void query05() throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        String count = runner.query("select * from user ",new ScalarHandler<String>());
        System.out.println(count);
    }

    /**
     * ColumnListHandler:把ResultSet里的某一个字段所有值转换成List，默认是第一列
     * 可以通过列的索引号（从1开始）或者通过字段名称，来获取指定列的所有值
     *
     * @throws SQLException
     */
    @Test
    public void query06() throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        List<String> list = runner.query("select * from user", new ColumnListHandler<String>("password"));
        System.out.println(list);
    }
    /**
     * ArrayHandler：把ResultSet里的第一行数据，转换成数组Object[]
     * ArrayHandler
     */
    @Test
    public void query07() throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        Object[] list= runner.query("select * from user", new ArrayHandler());
        for (Object o : list) {

            System.out.println(o);
        }
    }

    /**
     * ArrayListHandler:把ResultSet里的所有数据转换成List，List里的每一个元素，都是一个object[]，表示一行数据
     */
    @Test
    public void query08() throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        List<Object[]> list= runner.query("select * from user", new ArrayListHandler());
        System.out.println(list);
        for (Object[] o : list) {
            for (Object o1 : o) {
                System.out.print(o1+"    ");
            }
            System.out.println();
        }


    }


    /**
     * KeyedHandler:把ResultSet的所有数据转换成Map<第一列的值，Map<columnName, columnValue>>
     *
     * @throws SQLException
     */
    @Test
    public void query09() throws SQLException {
        QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
        //Map<Integer, Map<String, Object>> map = runner.query("select * from user", new KeyedHandler<Integer>());
        Map<Integer,Map<String,Object>>map = runner.query("select * from user", new KeyedHandler<Integer>());
        System.out.println(map);



    }






}
